//Documentation complete
/**
 * @author Jason Funk
 * @version 2009MR05
 */

package broker;

import problemDomain.*;
import static problemDomain.ErrorBean.throwError;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import static java.sql.Types.*;

/**
 * <code>Broker</code> for <code>OrderBean</code>.
 */
public class OrderBroker extends Broker<OrderBean> {
	
	/* Attributes */
	
	private int newOrderID;
	
	/* END Attributes */
	
	
	/* Singleton Constructor */

	private static OrderBroker broker;
	
	/**
	 * Constructs an <code>OrderBroker</code>
	 */
	private OrderBroker () throws ErrorBean {
		insertSQL = 
		deleteOrderSQL = null;
		updateSQL =
		insertOrderItemSQL = 
		deleteOrderItemSQL = null;
	}
	
	/**
	 * Returns an instance of <code>OrderBroker</code>.
	 * @return an instance of <code>OrderBroker</code>
	 * @throws ErrorBean if the <code>Broker</code> is unavailable
	 */
	public synchronized static OrderBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new OrderBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private CallableStatement insertSQL;
	private CallableStatement getInsertSQL () throws ErrorBean {
		CallableStatement statement = prepare(insertSQL, "CALL sp_create_order(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
		return registerOutParameter(statement, 12, SMALLINT);
	}
	
	private PreparedStatement updateSQL;
	private PreparedStatement getUpdateSQL () throws ErrorBean {
		return prepare(updateSQL, "UPDATE `order` SET fk_patron_id = ?, fk_employee_id = ?, order_status = ?, payment_status = ?, fk_delivery_id = ?, date_placed = ?, subtotal = ?, total = ?, gst = ?, pst = ?, balance = ? WHERE order_id = ?;");
	}
	
	private PreparedStatement insertOrderItemSQL;
	private PreparedStatement getInsertOrderItemSQL () throws ErrorBean {
		return prepare(insertOrderItemSQL, "INSERT INTO order_item SET fk_order_id = ?, fk_menu_item_id = ?, qty = ?;");
	}
	
	private CallableStatement deleteOrderSQL;
	private CallableStatement getDeleteOrderSQL () throws ErrorBean {
		return prepare(deleteOrderSQL, "CALL sp_delete_order(?);");
	}
	
	private PreparedStatement deleteOrderItemSQL;
	private PreparedStatement getDeleteOrderItemSQL () throws ErrorBean {
		return prepare(deleteOrderItemSQL, "DELETE FROM order_item WHERE fk_order_id = ?;");
	}
	
	private PreparedStatement selectOrderItemListSQL;
	private PreparedStatement getSelectOrderItemListSQL () throws ErrorBean {
		return prepare(selectOrderItemListSQL, "SELECT fk_menu_item_id, qty FROM order_item WHERE fk_order_id = ?;");
	}
	
	private CallableStatement addOrderItemSQL;
	private CallableStatement getAddOrderItemSQL () throws ErrorBean {
		return prepare(addOrderItemSQL, "CALL sp_add_order_item(?,?);");
	}
	
	private PreparedStatement removeOrderItemSQL;
	private PreparedStatement getRemoveOrderItemSQL () throws ErrorBean {
		return prepare(removeOrderItemSQL, "DELETE FROM order_item WHERE fk_order_id = ? AND fk_menu_item_id = ?");
	}
	
	private CallableStatement decreaseItemQuantitySQL;
	private CallableStatement getDecreaseItemQuantitySQL () throws ErrorBean {
		return prepare(decreaseItemQuantitySQL, "CALL sp_decrease_order_item_qty(?,?);");
	}
	
	private PreparedStatement addDeliverySQL;
	private PreparedStatement getAddDeliverySQL () throws ErrorBean {
		return prepare(addDeliverySQL, "UPDATE `order` SET fk_delivery_id = ? WHERE order_id = ?");
	}
	
	private PreparedStatement deleteDeliverySQL;
	private PreparedStatement getDeleteDeliverySQL () throws ErrorBean {
		return prepare(deleteDeliverySQL, "UPDATE `order` SET fk_delivery_id = null, order_type = 'Pickup' WHERE fk_delivery_id = ?");
	}
	
	/* END Prepared Statements */
	
	
	/* (Broker) Methods */
	
	@Override
	public List<OrderBean> listAll() throws ErrorBean {
		List<OrderBean> search = new ArrayList<OrderBean>();
		OrderBean ob;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * , time(date_placed) placedTime FROM `order` WHERE active = TRUE;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
			
				while (results.next()) {
					ob = new OrderBean();
					int order_id = results.getInt("order_id");
					ob.setId(order_id);
					//Set the employee and patron accounts
					ob.setPatron(getPatronAccountBean(results.getInt("fk_patron_id")));
					ob.setCreatedBy(getEmployeeAccountBean(results.getInt("fk_employee_id")));
					
					ob.setOrderStatus(results.getString("order_status"));
					ob.setPaymentStatus(results.getString("payment_status"));
					int deliveryId = results.getInt("fk_delivery_id");
					if (deliveryId > 0)
						ob.setDelivery(getDeliveryBean(deliveryId));
					else
						ob.setDelivery(null);
					Date date = results.getDate("date_placed");
					String time = results.getString("placedTime");
					ob.setOrderPlaced(convertDateToCalendar(date, time));
					// Set the list of Order Items
					ob.setOrderItemList(getOrderItemList(new OrderBean(order_id)));
					//Set the delivery
					
					ob.setGstAmount(results.getDouble("gst"));
					ob.setPstAmount(results.getDouble("pst"));
					ob.setBalancePaid(results.getDouble("balance"));
					
					search.add(ob);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}

	@Override
	public boolean persist (OrderBean ob) throws ErrorBean {
		ob.isValid();
		
		int order_id = ob.getId();
		
		int patron_id = 0; 
		if (ob.getPatron() != null)	
			patron_id = ob.getPatron().getId();
				
		int employee_id = 0;
		if (ob.getCreatedBy() != null)
			employee_id = ob.getCreatedBy().getID();
		String order_status = ob.getOrderStatus();
		String payment_status = ob.getPaymentStatus();
		Calendar dateP = ob.getOrderPlaced();
		Timestamp date_placed = new Timestamp(dateP.getTimeInMillis());
		int delivery_id = 0;
		String order_type = null;
		if (ob.getDelivery() != null) {
			delivery_id = ob.getDelivery().getId();
			order_type = "Delivery";
		}
		else
			order_type = "Pickup";
		double subtotal = ob.getSubtotal();
		double total = ob.getTotal();
		double gst = ob.getGstAmount();
		double pst = ob.getPstAmount();
		double balance = ob.getBalancePaid();
		List<OrderItemBean> orderItems = ob.getOrderItemList();
		
		
		if (order_id == 0) {
			try {
				CallableStatement insertSQL = getInsertSQL();
				if (patron_id > 0)
					insertSQL.setInt(1, patron_id);
				else 
					insertSQL.setNull(1, Types.INTEGER);
				if (employee_id > 0)
					insertSQL.setInt(2, employee_id);
				else
					insertSQL.setNull(2, Types.INTEGER);
				insertSQL.setString(3, order_status);
				insertSQL.setString(4, order_type);
				insertSQL.setString(5, payment_status);
				if (delivery_id > 0)
					insertSQL.setInt(6, delivery_id);
				else
					insertSQL.setNull(6, Types.INTEGER);
				insertSQL.setDouble(7, subtotal);
				insertSQL.setDouble(8, total);
				insertSQL.setDouble(9, gst);
				insertSQL.setDouble(10, pst);
				insertSQL.setDouble(11, balance);
				
				insertSQL.execute();
				
				int new_order_id = insertSQL.getInt(12);
				newOrderID = new_order_id;
				// Add menu items to the order item list
				if (orderItems != null && orderItems.size() != 0)
					for(int i=0; i< orderItems.size(); i++) {
						PreparedStatement insertOrderItemSQL = getInsertOrderItemSQL();
						insertOrderItemSQL.setInt(1, new_order_id);
						insertOrderItemSQL.setInt(2, orderItems.get(i).getId());
						insertOrderItemSQL.setInt(3, orderItems.get(i).getQuantity());
						insertOrderItemSQL.execute();
					}
				
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		} else if (order_id > 0) {
			try {
				PreparedStatement updateSQL = getUpdateSQL();
				updateSQL.setInt(1, patron_id);
				updateSQL.setInt(2, employee_id);
				updateSQL.setString(3, order_status);
				updateSQL.setString(4, payment_status);
				if (delivery_id > 0)
					updateSQL.setInt(5, delivery_id);
				else
					updateSQL.setNull(5, Types.INTEGER);
				updateSQL.setTimestamp(6, date_placed);
				updateSQL.setDouble(7, subtotal);
				updateSQL.setDouble(8, total);
				updateSQL.setDouble(9, gst);
				updateSQL.setDouble(10, pst);
				updateSQL.setDouble(11, balance);
				updateSQL.setInt(12, order_id);
				updateSQL.execute();
				
				if (orderItems != null && orderItems.size() != 0) {
					//Remove old order items from the order
					PreparedStatement deleteOrderItemSQL = getDeleteOrderItemSQL();
					deleteOrderItemSQL.setInt(1, order_id);
					deleteOrderItemSQL.execute();
					
					//Reinsert the updated order items
					for(int i=0; i< orderItems.size(); i++) {
						PreparedStatement insertOrderItemSQL = getInsertOrderItemSQL();
						insertOrderItemSQL.setInt(1, order_id);
						insertOrderItemSQL.setInt(2, orderItems.get(i).getId());
						insertOrderItemSQL.setInt(3, orderItems.get(i).getQuantity());
						insertOrderItemSQL.execute();
					}
				}
				
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}
		return false;
	}

	@Override
	public boolean remove (OrderBean ob) throws ErrorBean {
		int order_id = ob.getId();
		
		try {
			CallableStatement deleteOrderSQL = getDeleteOrderSQL();
			deleteOrderSQL.setInt(1, order_id);
			deleteOrderSQL.executeQuery();
			return true;
		} catch (SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}

	@Override
	public List<OrderBean> search (OrderBean searchBean) throws ErrorBean {
		List<OrderBean> search = new ArrayList<OrderBean>();
		OrderBean ob;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT *, time(date_placed) time_placed FROM `order` WHERE active = TRUE");
		
		//Search by order id
		int orderId = searchBean.getId();
		if (orderId > 0) {
			sql.append(" AND order_id = " + orderId);
		}
		else{
			//Search by Patron id
			int patron_id = searchBean.getPatron().getId();
			if (patron_id > 0) {
				sql.append(" AND fk_patron_id = " + patron_id);
			}
			
			//Search by Employee id
			EmployeeAccountBean employee = searchBean.getCreatedBy();
			if (employee != null) {
				int employee_id = employee.getID();
				if (employee_id > 0) {
					sql.append(" AND fk_employee_id = " + employee_id);
				}
			}
			
			//Search by Order status
			String order_status = searchBean.getOrderStatus();
			if (!"".equals(order_status) && order_status != null) {
				sql.append(" AND UPPER(order_status) LIKE UPPER('%" + order_status + "%')");
			}
			
			//Search by Payment status
			String payment_status = searchBean.getPaymentStatus();
			if (!"".equals(payment_status) && payment_status != null) {
				sql.append(" AND UPPER(payment_status) LIKE UPPER('%" + payment_status + "%')");
			}
		}
				
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					ob = new OrderBean();
					int order_id = results.getInt("order_id");
					ob.setId(order_id);
					
					//Set the employee and patron accounts
					ob.setCreatedBy(getEmployeeAccountBean(results.getInt("fk_employee_id")));
					ob.setPatron(getPatronAccountBean(results.getInt("fk_patron_id")));
					
					//Set the order placed date/time
					Date date_placed = results.getDate("date_placed");
					String time = results.getString("time_placed");
					ob.setOrderPlaced(convertDateToCalendar(date_placed, time));
					
					ob.setOrderStatus(results.getString("order_status"));
					ob.setPaymentStatus(results.getString("payment_status"));
					// Set the list of Order Items
					ob.setOrderItemList(getOrderItemList(new OrderBean(order_id)));
					//Set the delivery
					ob.setDelivery(getDeliveryBean(results.getInt("fk_delivery_id")));
					ob.setGstAmount(results.getDouble("gst"));
					ob.setPstAmount(results.getDouble("pst"));
					ob.setBalancePaid(results.getDouble("balance"));
					
					search.add(ob);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
	
	/* Accessors */
	
	/**
	 * Returns the new order ID.
	 * @return the newOrderID
	 */
	public int getNewOrderID () {
		return newOrderID;
	}
	
	/* END Accessors */
	
	
	/* Methods */
	
	/**
	 * Returns a list of items associated with an order.
	 * @param order the <code>OrderBean</code> to get the item list for.
	 * @return the list of items associated with an order.
	 * @throws ErrorBean if there is an retrieving the data.
	 */
	public List<OrderItemBean> getOrderItemList (OrderBean order) throws ErrorBean {
		List<OrderItemBean> orderItemList = new ArrayList<OrderItemBean>();
		
		try	{
			PreparedStatement stmt = getSelectOrderItemListSQL();
			stmt.setInt(1, order.getId());
			ResultSet results = stmt.executeQuery();
			
			while (results.next()) {			
				int menu_item_id = results.getInt("fk_menu_item_id");
				int quantity = results.getInt("qty");
				orderItemList.add(new OrderItemBean(getMenuItemBean(menu_item_id), quantity));
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return orderItemList;
	}
	
	/**
	 * Adds an order item to the order in the database.
	 * @param order the <code>OrderBean</code> that the order item is added to. 
	 * @param menuItem the <code>MenuItemBean</code> that is added to the order as an order item.
	 * @throws ErrorBean if there is a problem persisting the data
	 */
	public void addOrderItem(OrderBean order, MenuItemBean menuItem) throws ErrorBean {
		try {
			CallableStatement stmt = getAddOrderItemSQL();
			stmt.setInt(1, order.getId());
			stmt.setInt(2, menuItem.getId());
			stmt.execute();
		} catch (SQLException e) {
			throwError(
				BROKER_UPDATE_ERROR,
				e.getMessage(),
				this);
		}
	}
	
	/**
	 * Removes the order item from the order in the database.
	 * @param order the <code>OrderBean</code> that the order item is removed from. 
	 * @param menuItem the <code>MenuItemBean</code> that is removed from the order.
	 * @throws ErrorBean if there is a problem persisting the data
	 */
	public void removeOrderItem (OrderBean order, MenuItemBean menuItem) throws ErrorBean {
		try {
			PreparedStatement stmt = getRemoveOrderItemSQL();
			stmt.setInt(1, order.getId());
			stmt.setInt(2, menuItem.getId());
			stmt.execute();
		} catch (SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
		}
	}
	
	/**
	 * Decreases the quantity of the order item in the database by 1. 
	 * If the quantity was previously 1, the order item gets deleted.
	 * @param order the <code>OrderBean</code> whose order item's quantity is decreased. 
	 * @param menuItem the <code>MenuItemBean</code> whose quantity is decreased.
	 * @throws ErrorBean if there is a problem persisting the data
	 */
	public void decreaseItemQty (OrderBean order, MenuItemBean menuItem) throws ErrorBean {
		try {
			CallableStatement stmt = getDecreaseItemQuantitySQL();
			stmt.setInt(1, order.getId());
			stmt.setInt(2, menuItem.getId());
			stmt.execute();
		} catch (SQLException e) {
			throwError(
				BROKER_UPDATE_ERROR,
				e.getMessage(),
				this);
		}
	}
	
	/**
	 * Creates a connection between an order and delivery.
	 * @param order
	 * @param delivery
	 * @throws ErrorBean
	 */
	public void addDelivery (OrderBean order, DeliveryBean delivery) throws ErrorBean {
		try {
			PreparedStatement stmt = getAddDeliverySQL();
			stmt.setInt(1, delivery.getId());
			stmt.setInt(2, order.getId());
			stmt.execute();
		} catch (SQLException e) {
			throwError(
				BROKER_UPDATE_ERROR,
				e.getMessage(),
				this);
		}
	}
	
	/**
	 * Drops the delivery with specified ID from its order. 
	 * @param delivery the delivery to be dropped.
	 * @throws ErrorBean
	 */
	public void deleteDelivery (DeliveryBean delivery) throws ErrorBean {
		try {
			PreparedStatement stmt = getDeleteDeliverySQL();
			stmt.setInt(1, delivery.getId());
			stmt.execute();
		} catch (SQLException e) {
			throwError(
				BROKER_UPDATE_ERROR,
				e.getMessage(),
				this);
		}
	}
	
	/* END Methods */
	
	
	/* Local Methods */
	
	private DeliveryBean getDeliveryBean(int delivery_id) throws ErrorBean {
		List<DeliveryBean> deliveryList = new ArrayList<DeliveryBean>();
		
		DeliveryBean searchBean = new DeliveryBean();
		if (delivery_id > 0) {
			searchBean.setId(delivery_id);
			
			DeliveryBroker db = DeliveryBroker.getBroker();
			deliveryList = db.search(searchBean);
			
			if(!deliveryList.isEmpty())
				return deliveryList.get(0);
		}
		return null;
	}
	
	private EmployeeAccountBean getEmployeeAccountBean(int employee_id) throws ErrorBean {
		List<EmployeeAccountBean> employeeList = new ArrayList<EmployeeAccountBean>();
		
		EmployeeAccountBean searchBean = new EmployeeAccountBean();
		searchBean.setID(employee_id);
		
		EmployeeAccountBroker eab = EmployeeAccountBroker.getBroker();
		employeeList = eab.search(searchBean);
		if(!employeeList.isEmpty())
			return employeeList.get(0);
		return null;
	}
	
	private PatronAccountBean getPatronAccountBean(int patron_id) throws ErrorBean {
		List<PatronAccountBean> patronList = new ArrayList<PatronAccountBean>();
		
		PatronAccountBean searchBean = new PatronAccountBean();
		searchBean.setId(patron_id);
		
		PatronAccountBroker pb = PatronAccountBroker.getBroker();
		patronList = pb.search(searchBean);
		if(!patronList.isEmpty())
			return patronList.get(0);
		return null;
	}
	
	private MenuItemBean getMenuItemBean(int menu_item_id) throws ErrorBean {
		List<MenuItemBean> menuItemList = new ArrayList<MenuItemBean>();
		
		MenuItemBean searchBean = new MenuItemBean();
		searchBean.setId(menu_item_id);
		
		MenuItemBroker mib = MenuItemBroker.getBroker();
		menuItemList = mib.search(searchBean);
		
		if(!menuItemList.isEmpty())
			return menuItemList.get(0);
		return null;
	}
	
	/**
	 * Convert a Date object to a Calendar object.
	 * @param date The Date to convert
	 * @return c the converted Calendar
	 */
	private Calendar convertDateToCalendar (Date date, String time) {
		Calendar c = Calendar.getInstance();
		c.setTime(date);
		String [] fields = time.split(":");
		c.set(Calendar.HOUR, Integer.parseInt(fields[0]));
		c.set(Calendar.MINUTE, Integer.parseInt(fields[1]));
		return c;
	}
	
	/* END Local Methods */
	
}
